SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `ACME` DEFAULT CHARACTER SET latin1 ;
USE `ACME` ;

-- -----------------------------------------------------
-- Table `ACME`.`apartment`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`apartment` (
  `apartment_id` INT(10) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `address` VARCHAR(45) NOT NULL ,
  `city` VARCHAR(45) NOT NULL ,
  `open_from` DATETIME NOT NULL ,
  `open_till` DATETIME NOT NULL ,
  `day_cost_per_person` FLOAT NOT NULL ,
  PRIMARY KEY (`apartment_id`) )
ENGINE = MyISAM
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ACME`.`apartment_booking`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`apartment_booking` (
  `booking_id` VARCHAR(45) NOT NULL ,
  `apartment_id` INT(10) NOT NULL ,
  `booked_from` DATETIME NOT NULL ,
  `booked_till` DATETIME NOT NULL ,
  `booking_fee` FLOAT NOT NULL ,
  PRIMARY KEY (`booking_id`) ,
  INDEX `fk_apartment_booking` (`apartment_id` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ACME`.`carbookings`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`carbookings` (
  `booking_id` VARCHAR(45) NOT NULL ,
  `car_id` INT(11) NOT NULL ,
  `booked_from` DATE NOT NULL ,
  `booked_till` DATE NOT NULL ,
  `booking_fee` FLOAT NOT NULL ,
  PRIMARY KEY (`booking_id`) ,
  INDEX `fk_carbookings_rentalcars` (`car_id` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ACME`.`flightbookings`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`flightbookings` (
  `booking_id` VARCHAR(45) NOT NULL ,
  `plane_id` INT(11) NOT NULL ,
  `travel_date` DATE NOT NULL ,
  `booking_fee` FLOAT NOT NULL ,
  PRIMARY KEY (`booking_id`) ,
  INDEX `fk_flightbookings_planes` (`plane_id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 9
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ACME`.`planes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`planes` (
  `plane_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `company` VARCHAR(45) NOT NULL ,
  `start_city` VARCHAR(45) NOT NULL ,
  `target_city` VARCHAR(45) NOT NULL ,
  `tripcost` FLOAT NOT NULL ,
  `departure_time` TIME NOT NULL ,
  `arrival_time` TIME NOT NULL ,
  PRIMARY KEY (`plane_id`) )
ENGINE = MyISAM
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ACME`.`rentalcars`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`rentalcars` (
  `car_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `model` VARCHAR(45) NOT NULL ,
  `car_type` VARCHAR(45) NOT NULL ,
  `costs_per_day` FLOAT NOT NULL ,
  `city` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`car_id`) ,
  INDEX `fk_rentalcars_1` (`car_id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ACME`.`userbookings`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`userbookings` (
  `booking_id` VARCHAR(45) NOT NULL ,
  `user_id` INT(11) NOT NULL ,
  `apartment_booking_id` VARCHAR(45) NOT NULL ,
  `start_flight_booking_id` VARCHAR(45) NOT NULL ,
  `return_flight_booking_id` VARCHAR(45) NOT NULL ,
  `car_booking_id` VARCHAR(45) NULL DEFAULT NULL ,
  `start_date` DATETIME NOT NULL ,
  `return_date` DATETIME NOT NULL ,
  `amount_paid` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`booking_id`) ,
  INDEX `fk_userbookings_apartment` (`apartment_booking_id` ASC) ,
  INDEX `fk_userbookings_car` (`car_booking_id` ASC) ,
  INDEX `fk_userbookings_startflight` (`start_flight_booking_id` ASC) ,
  INDEX `fk_userbookings_returnflight` (`return_flight_booking_id` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `ACME`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ACME`.`users` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `creditcard_number` INT(11) NOT NULL ,
  PRIMARY KEY (`user_id`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = latin1;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
